IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GetBreadCrumb')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GetBreadCrumb
GO

/****** Object:  StoredProcedure [dbo].[USP_GetBreadCrumb]    Script Date: 10/21/2010 21:22:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		AKE
-- Create date: 11:49 13/9/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GetBreadCrumb]
	-- Add the parameters for the stored procedure here
	@sub_menu_code int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--declare @sub_menu_url int
	--set		@sub_menu_url = ''
	
	declare @bread_crumb nvarchar(max)
	
    -- Modified by Boonyarit Yingvoragan
    -- Modified date 20/10/2553
	select	@bread_crumb = 
			main.main_system_name + ' -> ' 
			+ menu.menu_system_name + ' -> ' 
			+ sub.sub_menu_name
			
	from	stp_sub_menu as sub
	join	stp_menu_system as menu on sub.menu_system_code = menu.menu_system_code
	join	stp_main_system as main on menu.main_system_code = main.main_system_code
	join	master_type as t on sub.type_code = t.type_code
	where	@sub_menu_code = sub.sub_menu_code
	
	
	select @bread_crumb
	
END


GO
